# -*- coding:utf-8 -*-
#!/usr/bin/python3

# -*- coding: UTF-8 -*-
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from decimal import *


import datetime
import mysql.connector

# 创建链接数据库
config1 = {'host': '192.168.1.101',  # 默认127.0.0.1
          'user': 'root', 'password': 'Ccii@562>c', 'port': 3306,  # 默认即为3306
          'database': 'ccii_redesign', 'charset': 'utf8'  # 默认即为utf8
          }

# 线上服务器连接
config = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }

# 本地连接
config2 = {'host': 'rm-2zefd6473rz062234ro.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }


# 订单金额
orderAmount = []
# 询价单金额
enquiryOrderAmount = []
# 全年金额
yearAmountTotal = Decimal(0.00)

# 定义昨天的日期
yesterDayDate = ''

# 查询普通订单每天金额
def findEveryDayOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询养颐普通订单每天金额
def findYangYiEveryDayOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s
                AND o.store_id = 101
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

# 查询POP普通订单每天金额
def findPopEveryDayOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s
                AND o.store_id <> 101 AND o.store_id <> 27225
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询每天佣金金额
def findFeeEveryDayAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                
                SELECT
                    SUM(cope_with_commission) total
                FROM
                    pay_commission
                WHERE audit_state = 2
                AND DATE_FORMAT(create_time, '%Y-%m-%d') = %s
                
                '''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data




# 查询普通订单每天金额
def findEveryYearOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询养颐订单年金额
def findYangYiEveryYearOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND o.store_id = 101
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP订单年金额
def findPopEveryYearOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND o.store_id <> 101 AND o.store_id <> 27225
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP订单年金额
def findPopEveryYearOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    sum(p.amount) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND o.store_id <> 101  AND o.store_id <> 27225
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询每年佣金总计
def findFeeEveryYearAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    SUM(cope_with_commission) total
                FROM
                    pay_commission
                WHERE audit_state = 2
                AND
                    DATE_FORMAT(create_time, '%Y') = %s
                
                '''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

# 查询普通订单每天数量
def findEveryDayOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    count(*) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询Pop订单每天支付数量
def findPopEveryDayOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    count(*) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND o.store_id <> 101 AND o.store_id <> 27225
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询Pop订单每年支付数量
def findPopEveryYearOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                    count(*) total
                FROM
                    shop_order o
                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                WHERE
                    o.status in (2,3,4,5)
                AND o.type = 0
                AND o.store_id <> 101 AND o.store_id <> 27225
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s
                AND p.pay_state = 3'''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



# 查询询价订单每天金额
def findEveryDayEnquiryOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  count(*)
                  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP询价订单每年数量
def findPopEveryYearEnquiryOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  count(*)
                  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND ep.store_id <> 101 AND ep.store_id <> 27225
                AND p.pay_state = 3
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



# 查询POP询价订单每天金额
def findPopEveryDayEnquiryOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  count(*)
                  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND ep.store_id <> 101  AND ep.store_id <> 27225 
                AND p.pay_state = 3
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP询价订单每年数量
def findPopEveryYearEnquiryOrderCount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  count(*)
                  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND ep.store_id <> 101  AND ep.store_id <> 27225 
                AND p.pay_state = 3
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询询价订单每天金额
def findEveryDayEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  CASE ep.is_earnest
                  WHEN 0 THEN sum(ep.seller_price) 
                  ELSE sum(ep.real_price) 
                  END
                  total
                    
                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s
                
               '''
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询会员支付，文章等增值服务费
def findEveryDayMemberPayAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect (**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print ('数据库链接失败！', str (e))
    else:  # try没有异常的时候才会执行
        print ("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor (buffered = True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  sum(p.amount) 
                  total

                FROM
                    pay_payment_flow p 
                WHERE
                    p.pay_state = 3
                AND p.business_type >= 30 AND ( p.business_type <= 39 OR p.business_type = 350 OR p.business_type = 100)
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s

               '''
        cursor.execute (stmt, params = (date,))
        data = cursor.fetchall ()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print ('查询数据报错！', str (e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close ()  # 关闭标记位
        cnn.close ()  # 关闭数据库链接

    return data


"""
查询购买电话次数相关的人数汇总-去重
@param typeIds 参数类型id数组  (32,33,34,35, 350, 36) 服务 100
"""
def findPayFeeUserCount(typeIds):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print ('数据库链接失败！', str (e))
    else:  # try没有异常的时候才会执行
        print ("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = f'''
                SELECT
                  p.id
                FROM
                    pay_payment_flow p 
                WHERE
                    p.pay_state = 3
                AND p.business_type in ({', '.join(['%s'] * len(typeIds))})
                
                GROUP BY p.user_id

               '''
        cursor.execute(stmt, typeIds)
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

# 查询行情通/买卖通会员付费人数
def findMemberFeeUserCount(memberId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                 sum(p.amount) total
                FROM
                 pay_payment_flow p LEFT JOIN shop_member_pay_order o on p.order_id = o.order_sn
                WHERE
                 p.pay_state = 3
                AND o.member_grade_id = %s
                GROUP BY p.user_id

               '''
        cursor.execute(stmt, params=(memberId,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



# 查询会员支付，文章等增值服务费-每年
def findEveryYearMemberPayAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect (**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print ('数据库链接失败！', str (e))
    else:  # try没有异常的时候才会执行
        print ("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor (buffered = True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  sum(p.amount) 
                  total

                FROM
                    pay_payment_flow p 
                WHERE
                    p.pay_state = 3
                AND p.business_type >= 30 AND ( p.business_type <= 39 OR p.business_type = 100 OR p.business_type = 350 )
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s

               '''
        cursor.execute (stmt, params = (date,))
        data = cursor.fetchall ()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print ('查询数据报错！', str (e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close ()  # 关闭标记位
        cnn.close ()  # 关闭数据库链接

    return data


# 查询养颐询价订单每天金额
def findYangYiEveryDayEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  CASE ep.is_earnest
                  WHEN 0 THEN sum(ep.seller_price) 
                  ELSE sum(ep.real_price) 
                  END
                  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND ep.store_id = 101
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询Pop询价订单每天金额
def findPopEveryDayEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                   sum(p.amount)  total
                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND ep.store_id <> 101  AND ep.store_id <> 27225
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP询价订单每天金额
def findPopEveryDayEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                  CASE ep.is_earnest
                  WHEN 0 THEN sum(ep.seller_price) 
                  ELSE sum(ep.real_price) 
                  END
                  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND ep.store_id <> 101 AND ep.store_id <> 43222
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y-%m-%d"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询询价订单每年金额
def findEveryYearEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                 
                sum(p.amount)  total

                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询养颐询价订单每年金额
def findYangYiEveryYearEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT          
                    sum(p.amount)  total
                FROM
                        shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                        ep.status >= 5
                AND ep.store_id = 101
                AND p.pay_state = 3
                AND DATE_FORMAT(
                        p.last_modified_date,
                        "%Y"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP询价订单每年金额
def findPopEveryYearEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                
                SELECT
                    sum(p.amount) total
                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND ep.store_id <> 101  AND ep.store_id <> 27225
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchone()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询POP询价订单每年金额
def findPopEveryYearEnquiryOrderAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                SELECT
                 	sum(p.amount) total
                FROM
                    shop_product_enquiry ep
                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                WHERE
                    ep.status >= 5
                AND p.pay_state = 3
                AND ep.store_id <> 101 AND ep.store_id <> 27225 
                AND DATE_FORMAT(
                    p.last_modified_date,
                    "%Y"
                ) = %s

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询一级品类
def findCategoryRootNames():
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''

                SELECT name 
                FROM  shop_product_category a WHERE grade = 0 AND enable_status = 1 ORDER BY orders

               '''
        cursor.execute(stmt, params=())
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询按品类
def findEveryDayCategoryGroupAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
        
                SELECT a.name name, sum(a.total) total
                FROM 
                (
                    ( 
                    SELECT
                            -- p.amount total, 
                            (soi.price * soi.quantity) total, 
                    
                    -- o.id,
                    -- sp.id,
                    -- sp.product_category_id,
                    spc1.`name`
                    -- spc1.category_code
                    FROM
                            shop_order o
                    LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                    LEFT JOIN shop_order_item soi on o.id= soi.orders
                    LEFT JOIN shop_sku sku on soi.sku_id = sku.id
                    LEFT JOIN shop_product sp on sku.product_id = sp.id
                    LEFT JOIN shop_product_category spc on sp.product_category_id = spc.id
                    LEFT JOIN shop_product_category spc1 ON spc.parent_id = spc1.id
                    WHERE
                            o.status in (2,3,4,5)
                    AND o.type = 0
                    AND DATE_FORMAT(
                            p.last_modified_date,
                            "%Y-%m-%d"
                    ) = %s
                    AND p.pay_state = 3
                    AND o.store_id <> 101 AND o.store_id <> 27225
                    )
                
                
                UNION ALL
                
                    (
                    SELECT
                        CASE ep.is_earnest
                        WHEN 0 THEN ep.seller_price
                        ELSE ep.real_price
                        END
                        total,
                      spc1.name
                            
                    FROM
                            shop_product_enquiry ep
                    LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                    LEFT JOIN shop_product sp on ep.product_id = sp.id
                    LEFT JOIN shop_product_category spc on sp.product_category_id = spc.id
                    LEFT JOIN shop_product_category spc1 ON spc.parent_id = spc1.id
                    WHERE
                            ep.status >= 5
                    AND ep.store_id <> 101 AND ep.store_id <> 27225
                    AND p.pay_state = 3
                    AND DATE_FORMAT(
                            p.last_modified_date,
                            "%Y-%m-%d"
                    ) = %s
                    )
                ) a
                
                GROUP BY a.name
               '''
        cursor.execute(stmt, params=(date, date))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询金额按商品销量
def findEveryDayProductAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                 SELECT a.name name, sum(a.total) total
                    FROM 
                    (
                            ( 
                            SELECT
                                -- p.amount total, 
                                (soi.price * soi.quantity) total, 
                                
                                -- o.id,
                                -- sp.id,
                                -- sp.product_category_id,
                                sp.`name` name,
                                sp.id id
                            -- spc1.category_code
                            FROM
                                shop_order o
                            LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                            LEFT JOIN shop_order_item soi on o.id= soi.orders
                            LEFT JOIN shop_sku sku on soi.sku_id = sku.id
                            LEFT JOIN shop_product sp on sku.product_id = sp.id
                            WHERE
                                            o.status in (2,3,4,5)
                            AND o.type = 0
                            AND DATE_FORMAT(
                                            p.last_modified_date,
                                            "%Y-%m-%d"
                            ) = %s
                            AND p.pay_state = 3
                            AND sp.store_id <> 101 AND sp.store_id <> 27225
                            
                            )
                    
                    UNION ALL
                    
                            (
                            SELECT
                                    CASE ep.is_earnest
                                    WHEN 0 THEN ep.seller_price
                                    ELSE ep.real_price 
                                    END
                                    total,
                                sp.name name,
                                sp.id id
                                            
                            FROM
                                shop_product_enquiry ep
                            LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                            LEFT JOIN shop_product sp on ep.product_id = sp.id
                            WHERE
                                  ep.status >= 5
                            AND ep.store_id <> 101 AND ep.store_id <> 27225
                            AND p.pay_state = 3
                            AND DATE_FORMAT(
                                            p.last_modified_date,
                                            "%Y-%m-%d"
                            ) = %s
                            )
                    ) a
                    
                    GROUP BY a.id 
                    ORDER BY total DESC 
                    LIMIT 10
                
               '''
        cursor.execute(stmt, params=(date, date))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询金额按店铺销量
def findEveryDayStoreAmount(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                 SELECT a.name name, sum(a.total) total
                        FROM 
                        (
                                ( 
                                SELECT
                                -- p.amount total, 
                                   (soi.price * soi.quantity) total, 
                                
                                -- o.id,
                                -- sp.id,
                                -- sp.product_category_id,
                                store.`name` name,
                                store.id id
                                -- spc1.category_code
                                FROM
                                                shop_order o
                                LEFT JOIN pay_payment_flow p ON o.sn = p.order_id
                                LEFT JOIN shop_order_item soi on o.id= soi.orders
                                LEFT JOIN shop_sku sku on soi.sku_id = sku.id
                                LEFT JOIN shop_product sp on sku.product_id = sp.id
                                LEFT JOIN shop_store store on o.store_id = store.id
                    
                                WHERE
                                    o.status in (2,3,4,5)
                                AND o.type = 0
                                AND o.store_id <> 101 AND o.store_id <> 27225
                                AND DATE_FORMAT(
                                                p.last_modified_date,
                                                "%Y-%m-%d"
                                ) = %s
                                AND p.pay_state = 3
                                
                                )
                        
                        
                        UNION ALL
                        
                                (
                                SELECT
                                        CASE ep.is_earnest
                                        WHEN 0 THEN ep.seller_price 
                                        ELSE ep.real_price 
                                        END
                                        total,
                                    store.name name,
                            store.id id
                                                
                                FROM
                                                shop_product_enquiry ep
                                LEFT JOIN pay_payment_flow p ON ep.sn = p.order_id
                                LEFT JOIN shop_product sp on ep.product_id = sp.id
                                LEFT JOIN shop_store store on ep.store_id = store.id
                          
                                WHERE
                                    ep.status >= 5
                                AND ep.store_id <> 101 AND ep.store_id <> 27225
                                AND p.pay_state = 3
                                AND DATE_FORMAT(
                                                p.last_modified_date,
                                                "%Y-%m-%d"
                                ) = %s
                                )
                        ) a
                        
                        GROUP BY a.id ORDER BY total DESC 
                        LIMIT 10

               '''
        cursor.execute(stmt, params=(date, date))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询金额按店铺销量
def findSearchWord(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                    SELECT
                        name,
                        count(*) total
                    FROM
                        shop_supply_demand_find_log
                    WHERE
                    DATE_FORMAT(create_date,"%Y-%m-%d") = %s
                    GROUP BY
                        `name`
                    ORDER BY
                        total DESC
                    LIMIT 10
                    
               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 每天商品访问品类TOP 10
def findEveryDayProductVisit(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = '''
                    SELECT
                        spc.`name` secondCategoryName,
                        spc1.name firstCategoryName,
                        count(*) total
                    
                            FROM
                            shop_message_log sml
                            LEFT JOIN shop_product sp on sml.data_id = sp.id
                            LEFT JOIN shop_product_category spc on sp.product_category_id = spc.id
                            LEFT JOIN shop_product_category spc1 on spc.parent_id = spc1.id
                    
                    WHERE
                            sml.del_flag = '0' AND sml.type = 30
                        AND  DATE_FORMAT(sml.create_date,"%Y-%m-%d") = %s
                    
                    GROUP BY sp.product_category_id
                    
                            ORDER BY
                            total DESC
                    LIMIT 10

               '''
        cursor.execute(stmt, params=(date,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
保存邮件数据内容到数据库
'''

def saveEmailContent(content = ''):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        item = []
        item.append(content)
        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'insert into data_email_report(content,create_date, type, del_flag) values (%s, NOW(), 1, 0)'
        cursor.execute(stmt, item)
        cnn.commit()
        print("插入数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接



# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset)\
        # .strftime('%Y-%m-%d')
    # print(re_date)
    return re_date


'''
获取普通订单金额html
'''
def getOrderAmountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    todayDate = getdate(0)

    content = "<span style='font-size:16px;color:#e92e2e;'>数据生成日期：" + todayDate.strftime('%Y-%m-%d') + "</span><br/>"
    content += "<br/>一、全站交易<br/>"
    content += "【全站】近8天订单金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"
    content+="<td style='text-align:center' >" + yesterDayDate.strftime('%Y')  + "年累计</td>"
    content+="</tr><tr><td style='text-align:center'>金额</td>"

    # 每天订单和询价订单总计
    finalAmount = []
    for date in date_arr:
        arr = findEveryDayOrderAmount(date.strftime('%Y-%m-%d'))
        arrEnquiry = findEveryDayEnquiryOrderAmount(date.strftime('%Y-%m-%d'))
        total = Decimal(0.00)
        if arr[0][0] is not None:
            orderAmount.append(arr[0][0])
            total = total + Decimal(arr[0][0])
        else:
            orderAmount.append(0)

        if arrEnquiry[0][0] is not None:
            enquiryOrderAmount.append(arrEnquiry[0][0])
            total = total + Decimal(arrEnquiry[0][0])
        else:
            enquiryOrderAmount.append(0)

        finalAmount.append(total)

    for item in finalAmount:
        if item is not None:
            content += "<td style='text-align:center' >" + str(format(item,',.2f')) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str('0.00') + "</td>"

    yearOrder = findEveryYearOrderAmount(yesterDayDate.strftime('%Y'))
    yearEnquiryOrder = findEveryYearEnquiryOrderAmount(yesterDayDate.strftime('%Y'))
    global yearAmountTotal
    if yearOrder[0][0] is not None:
        yearAmountTotal += yearOrder[0][0]

    if yearEnquiryOrder[0][0] is not None:
        yearAmountTotal += yearEnquiryOrder[0][0]

    content+= "<td style='text-align:center' >" + str(format(yearAmountTotal,',.2f')) + "</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取养颐近8天订单金额html
'''
def getYangYiOrderAmountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    todayDate = getdate(0)

    content = ""
    content += "<br/>二、自营交易<br/>"
    content += "【养颐自营】近8天交易金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"
    content+="<td style='text-align:center' >" + yesterDayDate.strftime('%Y')  + "年累计</td>"
    content+="</tr><tr><td style='text-align:center'>金额</td>"

    # 养颐每天订单和询价订单总计
    finalAmount = []
    for date in date_arr:
        arr = findYangYiEveryDayOrderAmount(date.strftime('%Y-%m-%d'))
        arrEnquiry = findYangYiEveryDayEnquiryOrderAmount(date.strftime('%Y-%m-%d'))
        total = Decimal(0.00)
        if arr[0][0] is not None:
            total = total + Decimal(arr[0][0])

        if arrEnquiry[0][0] is not None:
            total = total + Decimal(arrEnquiry[0][0])

        finalAmount.append(total)

    for item in finalAmount:
        if item is not None:
            content += "<td style='text-align:center' >" + str(format(item, ',.2f')) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str('0.00') + "</td>"

    yearOrder = findYangYiEveryYearOrderAmount(yesterDayDate.strftime('%Y'))
    yearEnquiryOrder = findYangYiEveryYearEnquiryOrderAmount(yesterDayDate.strftime('%Y'))
    YangYiyearAmountTotal = Decimal(0.00)
    if yearOrder[0][0] is not None:
        YangYiyearAmountTotal += yearOrder[0][0]

    if yearEnquiryOrder[0][0] is not None:
        YangYiyearAmountTotal += yearEnquiryOrder[0][0]

    content+= "<td style='text-align:center' >" + str(format(YangYiyearAmountTotal, ',.2f')) + "</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取POP平台近8天订单金额html-不包括养颐和融界
'''
def getPopOrderAmountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    todayDate = getdate(0)

    content = ""
    content += "<br/>三、POP平台交易<br/>"
    content += "【POP平台】近8天交易金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"
    content+="<td style='text-align:center' >" + yesterDayDate.strftime('%Y')  + "年累计</td>"
    content+="</tr><tr><td style='text-align:center'>金额</td>"

    # 每天订单和询价订单总计
    finalAmount = []
    for date in date_arr:
        arr = findPopEveryDayOrderAmount(date.strftime('%Y-%m-%d'))
        arrEnquiry = findPopEveryDayEnquiryOrderAmount(date.strftime('%Y-%m-%d'))
        total = Decimal(0.00)
        if arr[0][0] is not None:
            total = total + Decimal(arr[0][0])
        else:
            orderAmount.append(0)

        if arrEnquiry[0][0] is not None:
            total = total + Decimal(arrEnquiry[0][0])
        else:
            enquiryOrderAmount.append(0)

        finalAmount.append(total)

    for item in finalAmount:
        if item is not None:
            content += "<td style='text-align:center' >" + str(format(item, ',.2f')) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str('0.00') + "</td>"

    yearOrder = findPopEveryYearOrderAmount(yesterDayDate.strftime('%Y'))
    yearEnquiryOrder = findPopEveryYearEnquiryOrderAmount(yesterDayDate.strftime('%Y'))
    popAmountTotal = 0
    if yearOrder[0][0] is not None:
        popAmountTotal += yearOrder[0][0]

    if yearEnquiryOrder[0][0] is not None:
        popAmountTotal += yearEnquiryOrder[0][0]

    content+= "<td style='text-align:center' >" + str(format(popAmountTotal,',.2f')) + "</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取订单成交数量
'''
def getOrderCountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    # content = "<span style='font-size:16px;color:#e92e2e;'>数据生成日期：" + todayDate.strftime('%Y-%m-%d') + "</span><br/>"
    content = ""
    content += "<br/>近8天订单支付笔数统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="</tr><tr><td style='text-align:center'  >金额</td>"

    for date in date_arr:
        arr = findEveryDayOrderCount(date.strftime('%Y-%m-%d'))
        if arr[0][0] is not None:
            content += "<td style='text-align:center' >" + str(arr[0][0]) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str(0) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取POP订单支付数量
'''
def getPopOrderCountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    # content = "<span style='font-size:16px;color:#e92e2e;'>数据生成日期：" + todayDate.strftime('%Y-%m-%d') + "</span><br/>"
    content = ""
    content += "<br/>【POP平台】近8天订单支付统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' >" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content += "<td style='text-align:center' >" + yesterDayDate.strftime('%Y') + "年累计</td>"
    content+="</tr><tr><td style='text-align:center'  >数量</td>"

    countArr = []
    for date in date_arr:
        total = 0
        arr = findPopEveryDayOrderCount(date.strftime('%Y-%m-%d'))
        arrEnquiry = findPopEveryDayEnquiryOrderCount(date.strftime('%Y-%m-%d'))
        if arr[0][0] is not None:
            total+= arr[0][0]

        if arrEnquiry[0][0] is not None:
            total+= arrEnquiry[0][0]

        countArr.append(total)

    for item in countArr:
        if item is not None:
            content += "<td style='text-align:center' >" + str(format(item, ',')) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str(0) + "</td>"

    yearTotal = 0
    yearOrderCount = findPopEveryYearOrderCount(yesterDayDate.strftime('%Y'))
    yearEnauiryOrderCount = findPopEveryYearEnquiryOrderCount(yesterDayDate.strftime('%Y'))

    if yearOrderCount[0][0] is not None:
        yearTotal += yearOrderCount[0][0]

    if yearEnauiryOrderCount[0][0] is not None:
        yearTotal += yearEnauiryOrderCount[0][0]

    content += "<td style='text-align:center' >" + str(format(yearTotal,',')) + "</td>"
    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取普通询价订单金额html
'''
def getEnquiryOrderCountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = ""
    content += "<br/>近8天询价单支付笔数统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center;'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' width='100'>" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="</tr><tr><td style='text-align:center'  >金额</td>"

    for date in date_arr:
        arr = findEveryDayEnquiryOrderCount(date.strftime('%Y-%m-%d'))
        if arr[0][0] is not None:
            content += "<td style='text-align:center' >" + str(arr[0][0]) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str(0) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取普通询价订单金额html
'''
def getEnquiryOrderAmountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = ""
    content += "<br/>近8天询价订单金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center;'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' width='100'>" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="</tr><tr><td style='text-align:center'  >金额</td>"

    for date in date_arr:
        arr = findEveryDayEnquiryOrderAmount(date.strftime('%Y-%m-%d'))
        if arr[0][0] is not None:
            enquiryOrderAmount.append(arr[0][0])
            content += "<td style='text-align:center' >" + str(arr[0][0]) + "</td>"
        else:
            enquiryOrderAmount.append(0)
            content += "<td style='text-align:center' >" + str(0) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取近8天每天佣金金额
'''
def getOrderFeeAmountHtml():

    amount = []
    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    for date in date_arr:
        dayFee = findFeeEveryDayAmount(date.strftime('%Y-%m-%d'))
        if dayFee[0][0] is not None:
            amount.append(str(format(dayFee[0][0],',.2f')))
        else:
            amount.append ('0.00')

    content = ""
    content += "<br/>【全站】近8天佣金金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center;'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' width='100'>" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="<td style='text-align:center' width='100'>" + yesterDayDate.strftime('%Y') + "年累计</td>"
    content+="</tr><tr><td style='text-align:center'>金额</td>"

    for item in amount:
        if item is not None:
            content += "<td style='text-align:center' >" + str(item) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str("0.00") + "</td>"

    year = yesterDayDate.strftime ('%Y')
    yearAmountFeeTotal = findFeeEveryYearAmount(year)
    if yearAmountFeeTotal[0][0] is not None:
        content += "<td style='text-align:center' >" + str(format(yearAmountFeeTotal[0][0],',.2f')) + "</td>"
    else:
        content += "<td style='text-align:center' >" + str("0.00") + "</td>"


    content += "</tr></table>"
    orderAmount.clear()
    enquiryOrderAmount.clear()

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取近8天增值服务费用
'''
def getMemberPayAmountHtml(dayNum):

    date_num = [8,7,6,5,4,3,2,1]
    date_arr = []
    for num in date_num:
        date_arr.append(getdate(num))

    content = ""
    content += "<br/>【全站】近8天增值服务统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center;'>日期</td>"

    for date in date_arr:
        content += "<td style='text-align:center' width='100'>" + date.strftime('%m-%d')  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content += "<td style='text-align:center' width='100'>" + yesterDayDate.strftime ('%Y') + "年累计</td>"
    content+="</tr><tr><td style='text-align:center'  >金额</td>"

    for date in date_arr:
        arr = findEveryDayMemberPayAmount(date.strftime('%Y-%m-%d'))
        if arr[0][0] is not None:
            content += "<td style='text-align:center' >" + str(format(arr[0][0],',.2f')) + "</td>"
        else:
            content += "<td style='text-align:center' >" + str('0.00') + "</td>"


    yearAmount = findEveryYearMemberPayAmount(yesterDayDate.strftime('%Y'))

    if yearAmount[0][0] is not None:
        content += "<td style='text-align:center' >" + str (format (yearAmount[0][0] + 2999, ',.2f')) + "</td>"
    else:
        content += "<td style='text-align:center' >" + str ('0.00') + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content



"""
增值服务人数统计
"""
def getMemberPayCountHtml():
    type_arr = ["服务商入驻（开店）", "服务商入驻（服务）", "行情通", "买卖通"]
    content = ""
    content += "<br/>【全站】服务商入驻统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='1000'><tr><td style='text-align:center;'>类型</td>"

    for typeName in type_arr:
        content += "<td style='text-align:center'>" + typeName + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content += "</tr><tr><td style='text-align:center'>数量</td>"

    data_arr = []
    phoneTypeIds = [35, 350]
    data1 = findPayFeeUserCount(phoneTypeIds)
    serviceTypeIds = [100]
    data_arr.append(len(data1))

    data2 = findPayFeeUserCount(serviceTypeIds)
    # 有12个是其它付款方式
    data_arr.append(len(data2) + 12)

    data3 = findMemberFeeUserCount(4)
    data_arr.append(len(data3))

    data4 = findMemberFeeUserCount(5)
    data_arr.append(len(data4))

    for count in data_arr:
        content += "<td style='text-align:center' >" + str(count) + "</td>"

    content += "</tr></table>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取昨天按一级品类订单金额
'''
def getOrderCategoryGroupHtml(dayNum):
    paramDate = getdate(dayNum)

    data = findEveryDayCategoryGroupAmount(paramDate.strftime('%Y-%m-%d'))
    dataNames = []
    for item in data:
        dataNames.append(item[0])

    # categoryNames = ['猪类','牛类','羊类','鱼类','虾类','蟹类','贝类','鸡类','鸭类','其他']
    rootCats = findCategoryRootNames()
    cateNameArr = []
    for item in rootCats:
        cateNameArr.append(item[0])

    amountData = []
    for catName in cateNameArr:
        tableItem = []
        tableItem.append(catName)

        if catName in dataNames:
            index = dataNames.index(catName)
            tableItem.append(data[index][1])
        else:
            tableItem.append(0)

        amountData.append(tableItem)

    content = ""
    content += "<br/>【POP平台】昨日品类商品销售统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='500'><tr><td style='text-align:center;' width='100'>品类</td>"
    content += "<td style='text-align:center;' width='100'>金额</td></tr>"

    for item in amountData:
        content += "<tr><td style='text-align:center;'>" + item[0] + "</td><td style='text-align:center;'>" + format(item[1], ',.2f') + "</td></tr>"

    content += "</table>"

    # print(content)
    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content

'''
获取昨天按商品名称金额 top 5
'''
def getOrderProductHtml(dayNum):
    paramDate = getdate(dayNum)
    data = findEveryDayProductAmount(paramDate.strftime('%Y-%m-%d'))

    # indexArray = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    indexArray = [1, 2, 3, 4, 5]
    amountData = []
    for index in indexArray:
        size = len(data)
        item =[]
        item.append(index)
        if size >= index:
            item.append(data[index - 1][0])
            item.append(data[index - 1][1])
        else:
            item.append('-')
            item.append(0)

        amountData.append(item)

    content = ""
    content += "<br/>【POP平台】昨日单品销售金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='500'><tr><td style='text-align:center;' width='100'>序号</td>" \
               "<td style='text-align:center;' width='150'>商品</td><td style='text-align:center;' width='100'>金额</td></tr>"

    for item in amountData:
        content += "<tr><td style='text-align:center;'>" + str(item[0])+"</td>"
        content += "<td style='text-align:center;'>" + str(item[1])+"</td>"
        content += "<td style='text-align:center;'>" + str(format(item[2], ',.2f'))+"</td></tr>"

    content += "</table>"
    # print(content)
    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取昨天按店铺统计名称金额top5
'''
def getOrderStoreAmountHtml(dayNum):
    paramDate = getdate(dayNum)
    data = findEveryDayStoreAmount(paramDate.strftime('%Y-%m-%d'))

    # indexArray = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    indexArray = [1, 2, 3, 4, 5]
    amountData = []
    for index in indexArray:
        size = len(data)
        item =[]
        item.append(index)
        if size >= index:
            item.append(data[index - 1][0])
            item.append(data[index - 1][1])
        else:
            item.append('-')
            item.append(0)

        amountData.append(item)

    content = ""
    content += "<br/>【POP平台】昨日店铺销售金额统计:<br/>"
    content += "<table border='2' cellspacing='0'  width='500'><tr><td style='text-align:center;' width='100'>序号</td>" \
               "<td style='text-align:center;' width='150'>店铺</td><td style='text-align:center;' width='100'>金额</td></tr>"

    for item in amountData:
        content += "<tr><td style='text-align:center;'>" + str(item[0])+"</td>"
        content += "<td style='text-align:center;'>" + str(item[1])+"</td>"
        content += "<td style='text-align:center;'>" + str(format(item[2], ',.2f')) + "</td></tr>"

    content += "</table>"
    # print(content)
    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取昨天热搜词top10
'''
def getSearchWordHtml(dayNum):
    paramDate = getdate(dayNum)
    data = findSearchWord(paramDate.strftime('%Y-%m-%d'))

    indexArray = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    amountData = []
    for index in indexArray:
        size = len(data)
        item =[]
        item.append(index)
        if size >= index:
            item.append(data[index - 1][0])
            item.append(data[index - 1][1])
        else:
            item.append('-')
            item.append('0.00')

        amountData.append(item)

    content = ""
    content += "<br/>【全站】昨日搜索关键字top10:<br/>"
    content += "<table border='2' cellspacing='0'  width='500'><tr><td style='text-align:center;' width='100'>序号</td>" \
               "<td style='text-align:center;' width='150'>搜索词</td><td style='text-align:center;' width='100'>次数</td></tr>"

    for item in amountData:
        content += "<tr><td style='text-align:center;'>"+ str(item[0])+"</td>"
        content += "<td style='text-align:center;'>"+ str(item[1])+"</td>"
        content += "<td style='text-align:center;'>"+ str(format(item[2],','))+"</td></tr>"

    content += "</table>"
    # print(content)
    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


'''
获取昨天商品访问按品类汇总TOP10
'''
def getProductVisitHtml(dayNum):
    paramDate = getdate(dayNum)
    data = findEveryDayProductVisit(paramDate.strftime('%Y-%m-%d'))

    indexArray = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    amountData = []
    for index in indexArray:
        size = len(data)
        item =[]
        item.append(index)
        if size >= index:
            item.append(data[index - 1][0])
            item.append(data[index - 1][1])
            item.append(data[index - 1][2])
        else:
            item.append('-')
            item.append('-')
            item.append('0')

        amountData.append(item)

    content = ""
    content += "<br/>【全站】昨日商品访问品类汇总top10:<br/>"
    content += "<table border='2' cellspacing='0'  width='500'><tr><td style='text-align:center;' width='100'>序号</td>" \
               "<td style='text-align:center;' width='150'>二级品类名称</td>" \
               "<td style='text-align:center;' width='150'>一级品类名称</td>" \
               "<td style='text-align:center;' width='100'>访问次数</td></tr>"

    for item in amountData:
        content += "<tr><td style='text-align:center;'>"+ str(item[0])+"</td>"
        content += "<td style='text-align:center;'>"+ str(item[1])+"</td>"
        content += "<td style='text-align:center;'>"+ str(item[2])+"</td>"
        content += "<td style='text-align:center;'>"+ str(format(item[3],','))+"</td></tr>"

    content += "</table>"
    # print(content)
    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content



'''
发送邮件
'''
def send_email(content):
    # 第三方 SMTP 服务
    mail_host = "smtp.exmail.qq.com"  # 设置服务器
    mail_user = "coldeal@cciigroup.com"  # 用户名
    mail_pass = "intlCCII2020"  # 口令

    sender = 'coldeal@cciigroup.com'
    receivers = ['songgx@cciigroup.com', 'renl@cciigroup.com', 'zhaon@cciigroup.com',

                 'liyf@cciigroup.com', 'liuyn@cciigroup.com', 'zhangzl@cciigroup.com',
                 'yuy@cciigroup.com', 'majq@cciigroup.com',
                 'tianxl@cciigroup.com', 'zangm@cciigroup.com',
                 'wangzw@cciigroup.com',
                 'dengpr@cciigroup.com', 'anjp@cciigroup.com',
                 'chenyz@cciigroup.com', 'hancy@cciigroup.com',
                 'lis@cciigroup.com'
                ]

    # receivers = ['lis@cciigroup.com']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
    # 邮件正文内容
    message = MIMEText(content, 'HTML', 'utf-8')
    # 发件人名称
    message['From'] = Header("coldeal@cciigroup.com", 'utf-8')

    # 收件人名称
    # message['To'] = Header("每日会员注册量", 'utf-8')

    # 邮件名称 Python SMTP 邮件测试
    subject = '每天平台交易数据'
    message['Subject'] = Header(subject, 'utf-8')
    print("开始发送")
    # try:
    smtpObj = smtplib.SMTP_SSL(mail_host)
    #smtpObj.connect(mail_host, 587)  # 25 为 SMTP 端口号
    #smtpObj.starttls()
    smtpObj.login(mail_user, mail_pass)
    for receiver in receivers:
        message['To'] = Header(receiver, 'utf-8')
        smtpObj.sendmail(sender, receiver, message.as_string())

    print("邮件发送成功")
    # except smtplib.SMTPException as e:
    #     print("Error: 无法发送邮件")
    #     print(e)

'''
主调用方法
'''
def work(dayNum):
    content = ''
    # content = getOrderAmountHtml(dayNum)
    # content += getEnquiryOrderAmountHtml(dayNum)
    # content += getOrderCountHtml(dayNum)
    # content += getEnquiryOrderCountHtml(dayNum)
    content += getOrderAmountHtml(dayNum)
    content += getOrderFeeAmountHtml()
    content += getMemberPayAmountHtml(dayNum)
    content += getMemberPayCountHtml()
    content += getYangYiOrderAmountHtml(dayNum)
    content += getPopOrderAmountHtml(dayNum)
    content += getPopOrderCountHtml(dayNum)
    content += getOrderCategoryGroupHtml(dayNum)
    content += getSearchWordHtml(dayNum)
    content += getProductVisitHtml(dayNum)
    content += getOrderProductHtml(dayNum)
    content += getOrderStoreAmountHtml(dayNum)
    print("----")
    print(content)
    print("----")
    send_email(content)

    try:
        saveEmailContent(content)
    except mysql.connector.Error as e:
        print('保存邮件数据异常', str(e))



if __name__ == "__main__":
    yesterDayDate = getdate(1)
    work(1)



